Kinds of Triggers

•  Data Modification Language (DML) triggers
•  Data Definition Language (DDL) triggers

Data Modification Language (DML) triggers

Data Modification Language triggers is fired whenever data in the underlying table is affected by any of the Data Manipulation Language (DML) statements -INSERT, UPDATE OR DELETE.DML trigger is use to maintaining consistent, reliable and correct data in tables.

DML triggers characteristics:

•   Fired automatically by the SQL Server
•   Trigger cannot explicitly invoked or executed
•   It cannot return data to the user.

Whenever trigger fired two tables are created:

•  Inserted
•  Deleted

The Inserted table contains a copy of all records that are inserted in the trigger table. The Deleted table contains all records that have been deleted from the trigger table. This table is used to refer old values.
Whenever any updating takes place, the trigger uses both the inserted and deleted tables.

Insert trigger: Is fired whenever an attempt is made to insert a row in the trigger table.

Delete trigger:  Is fired whenever an attempt is made to delete a row in the trigger table.

Update trigger: Is fired when Update statements executed in the trigger table .It uses two logical tables for its operations, the deleted table that contains the original rows and the inserted table that stores the new rows.

Data Definition Language (DDL) triggers

A Data Definition Language trigger is fired in response to Data Definition Language, such as create table or alter table .DDL triggers can be used to perform database auditing, administrative tasks.

DDL trigger are categorized as:

•   After Triggers
•   Instead of Triggers
•   Nested Triggers
•   Recursive Triggers

After Trigger

It is a specialized stored procedure that is executed when data in the table associated with trigger is modified. After trigger executes the code associated with it after the event for which it is made happens. You can have multiple after triggers for any single DML operations.

<%--

--%>

Instead of Trigger

The instead of triggers can be primarily used to perform an action , such as a DML operation on another table or view .This type of trigger can be created on both a table as well as view .

Nested Trigger

Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels. You can control whether AFTER triggers can be nested through the Nested Triggers server configuration option. INSTEAD OF triggers (only DML triggers can be INSTEAD OF triggers) can be nested regardless of this setting.

Recursive Triggers

Recursive triggers when a trigger fires and performs a statement that will cause the same trigger to fire, recursion will occur. There are two triggers, Direct and Indirect.

How to Create Trigger

CreateTrigger trigger_name
      On{Object Name}
{For|After|InsteadOf}{event _type[,..n]| DDl_Database_Level_Events}
      {AS
            {
             SQl_Staement[..n]
            }

For example I have one table Studentdetail to perform trigger operation

StudentDetail

Creating Insert Trigger

Query

createtrigger trg2 
on StudentDetail
forinsert
as
begin
      select*from StudentDetail
end

In StudentDetail inserting one new record after created insert trigger

Query

Insert StudentDetail values('S011','Imran','25','Varansi','U.P.')

Output

Creating Delete trigger

Query

createtrigger trg4 
on StudentDetail
fordelete
as
begin
      select*from StudentDetail
end

In Studentdetail table deleting one record after created delete trigger

Query

delete StudentDetail where Name='Imran'

Output

Creating Update Trigger

Query

createtrigger trg5 
on StudentDetail
forupdate
as
begin
      select*from StudentDetail
end

In studentdetail table updating one record after created update trigger

Query

update StudentDetail set City='Gorakhpur',State='U.P.'where Name='Pooja'

Output

Creating After Trigger

Query

createtrigger trg10
on StudentDetail
After
insert
as
begin
select*from StudentDetail
end

In studentdetail table inserting one record after created after trigger

Query

Insert StudentDetail values('S012','Vivek','25','Varansi','U.P.')

Output

Creating Instead of Trigger

Query

createtrigger trg12 on StudentDetail
 insteadofupdateas
begin
    select*from StudentDetail
end

In studentdetail table updating one record after created instead of trigger but you will see there is no updating performs on table there is cause of instead of trigger.

Query

update StudentDetail set City='Jaunpur'where Name='Raj'

Output